package com.yueyin.ui.main;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteHelper extends SQLiteOpenHelper {

    //数据库名
    private static final String DATABASE_NAME = "music";
    //版本 用于更新
    private static final int DATABASE_VERSION = 1;

    //歌曲表名
    private final String TABLE_MUSIC = "music";
    //歌词表名
    private final String TABLE_LRC = "lrc";

    private SQLiteDatabase db;

    public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String sql_music = "create table " + TABLE_MUSIC + " " +
                "(_id integer primary key," +
                "name varchar(64) not null," +
                "author varchar(32) default null," +
                "size bigint default null," +
                "time varchar(32) default null," +
                "path varchar(128) not null )";
        db.execSQL(sql_music);

        String sql_lrc = "create table " + TABLE_LRC + " " +
                "(_id integer primary key," +
                "name varchar(64) not null," +
                "path varchar(128) not null )";
        db.execSQL(sql_lrc);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int paramInt1,
                          int paramInt2) {
        // TODO Auto-generated method stub
        String sql_music = "drop table if exists " + TABLE_MUSIC;
        String sql_lrc = "drop table if exists " + TABLE_LRC;
        db.execSQL(sql_music);
        db.execSQL(sql_lrc);
        onCreate(db);
    }

    /**
     * 该方法可同时添加歌曲和歌词
     * 通过isLrc来区别
     **/
    public void add_File(Data data, boolean isLrc) {
        db = getWritableDatabase();

        if (isLrc) {
            //若已存在 则跳过
            if (isExists(data, true)) {
                ContentValues cv = new ContentValues();
                cv.put("name", data.getName());
                cv.put("path", data.getPath());
                db.insert(TABLE_LRC, null, cv);
            }
        } else {
            if (isExists(data, false)) {
                ContentValues cv = new ContentValues();
                cv.put("name", data.getName());
                cv.put("author", data.getAuthor());
                cv.put("size", data.getSize());
                cv.put("time", data.getTime());
                cv.put("path", data.getPath());
                db.insert(TABLE_MUSIC, null, cv);
            }
        }
    }

    //从系统数据库读取数据 判断数据是否在本应用的数据库已存在
    public boolean isExists(Data data, boolean isLrc) {
        db = getReadableDatabase();
        Cursor cursor = null;

        if (isLrc) {
            String sql_lrc = "select * from " + TABLE_LRC + " where name=? and path=?";
            cursor = db.rawQuery(sql_lrc, new String[]{data.getName(), data.getPath()});
        } else {
            String sql_music = "select * from " + TABLE_MUSIC + " where name=?";
            String[] param = {data.getName()};

            if (data.getAuthor() != null) {
                sql_music += " and author=?";
                param = new String[]{data.getName(), data.getAuthor()};
            }

            cursor = db.rawQuery(sql_music, param);
        }
        boolean flag = cursor.getCount() <= 0;

        cursor.close();

        return flag;
    }

    //通过歌名查找全部信息 。。。 主要是路径
    public Cursor findByName(String name, boolean isLrc) {
        db = getReadableDatabase();
        String sql_music = "select * from " + TABLE_MUSIC + " where name=?";
        String sql_lrc = "select * from " + TABLE_LRC + " where name=?";

        Cursor cursor = null;

        if (isLrc)
            cursor = db.rawQuery(sql_lrc, new String[]{name});
        else
            cursor = db.rawQuery(sql_music, new String[]{name});

        return cursor;
    }

    //查询数据库信息总数
    public Cursor selectCount() {
        db = getReadableDatabase();
        String sql = "select count(*) from " + TABLE_MUSIC;
        return db.rawQuery(sql, null);
    }


    //查找全部歌曲 以显示到listview
    public Cursor selectAll() {
        db = getReadableDatabase();
        String sql = "select * from " + TABLE_MUSIC + " order by author,name asc";
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    //清空表记录 用于更新数据
    public void clearTable() {
        db = getWritableDatabase();
        String sql_music = "drop table if exists " + TABLE_MUSIC;
        String sql_lrc = "drop table if exists " + TABLE_LRC;
        db.execSQL(sql_music);
        db.execSQL(sql_lrc);
        onCreate(db);

        closeDB();
    }

    public void closeDB() {
        if (db != null)
            db.close();
    }

}
